package com.example.pubsearch.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_NAME = "PubDB3";

	public MySQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// SQL statement to create pubs table
		String CREATE_PUB_TABLE = "CREATE TABLE pubs ( "
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "name TEXT, "
				+ "street TEXT, " + "phone TEXT, " + "homepage TEXT, "
				+ "openingHours TEXT, " + "latitude TEXT, "
				+ "longitude TEXT, " + "outdoorSpace TEXT, " + "notes TEXT )";

		// create books table
		db.execSQL(CREATE_PUB_TABLE);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older pubs table if existed
		db.execSQL("DROP TABLE IF EXISTS pubs");

		// create fresh pubs table
		this.onCreate(db);
	}

	public void deletePubs() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete("pubs", null, null);
		db.close();
	}

	public int updatePub(Pub pub) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put("notes", pub.getNotes());

		// updating row
		return db.update("pubs", values, " name = ?",
				new String[] { String.valueOf(pub.getName()) });
	}

	public int getPubsCount() {
		int count;
		String countQuery = "SELECT  * FROM pubs";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		count = cursor.getCount();
		cursor.close();
		db.close();
		// return count
		return count;
	}

	public void insertPubs() {
		// 1. get reference to writable DB
		SQLiteDatabase db = this.getWritableDatabase();

		// 2. create ContentValues to add key "column"/value
		ContentValues values = new ContentValues();
		values.put("name", "Admiral Sportwetten");
		values.put("street", "Bahnhofstraße 8");
		values.put("phone", "03172/42687-11");
		values.put("homepage", "https://www.admiralbet.com/DE/");
		values.put("openingHours", "Mo-So ab 11.00 h");
		values.put("latitude", "47.2153754");
		values.put("longitude", "15.6285170");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Alibi");
		values.put("street", "Kapruner Generator Straße 20");
		values.put("phone", "0676/7291333");
		values.put("homepage", "http://www.cafe-alibi.at/");
		values.put("openingHours", "Mo-Sa  ab 7.00 h");
		values.put("latitude", "47.2191601");
		values.put("longitude", "15.6277702");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Almrausch");
		values.put("street", "Dr.-Karl-Widdmann-Straße 4");
		values.put("phone", "n.a.");
		values.put("homepage", "http://bollwerk.at/");
		values.put("openingHours", "Do -Sa ab 21.00 h");
		values.put("latitude", "47.2213021");
		values.put("longitude", "15.6247465");
		values.put("outdoorSpace", "Nein");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Billiard The Pub");
		values.put("street", "Dr.-Karl-Widdmann-Straße 4");
		values.put("phone", "03172/3360");
		values.put("homepage", "");
		values.put("openingHours", "Mo-Sa 14.00 - 4.00 h, So 14.00-23.00 h");
		values.put("latitude", "47.2210535");
		values.put("longitude", "15.6249670");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Bismarkstüberl");
		values.put("street", "Bismarckgasse 9");
		values.put("phone", "0664/1487464");
		values.put("homepage", "");
		values.put("openingHours", "Mo-Sa 07.30 - 19.30 h, So 09.00 - 19.30 h");
		values.put("latitude", "47.218219");
		values.put("longitude", "15.624007");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Cafe Esprit");
		values.put("street", "Klammstraße 2");
		values.put("phone", "0676/7393273");
		values.put("homepage", "");
		values.put("openingHours", "Mo - Fr ab 16.00 h, Sa - So ab 18.00 h");
		values.put("latitude", "47.2179222");
		values.put("longitude", "15.6230562");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Evergreen Piano-Bar");
		values.put("street", "Mühlgasse 5");
		values.put("phone", "n.a.");
		values.put("homepage", "");
		values.put("openingHours", "n.a.");
		values.put("latitude", "47.2166581");
		values.put("longitude", "15.6255752");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Herby's Treff");
		values.put("street", "Marburger Straße 16");
		values.put("phone", "03172/41840");
		values.put("homepage", "");
		values.put("openingHours", "Di - So ab 13.00 h");
		values.put("latitude", "47.2159234");
		values.put("longitude", "15.6231947");
		values.put("outdoorSpace", "Nein");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Last Exit");
		values.put("street", "Lederergasse 1");
		values.put("phone", "03172/42620");
		values.put("homepage", "http://bollwerk.at/");
		values.put("openingHours", "Fr - Sa ab 20.00 h");
		values.put("latitude", "47.2175613");
		values.put("longitude", "15.6248620");
		values.put("outdoorSpace", "Nein");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "LIFE ART");
		values.put("street", "Rathausgasse 3");
		values.put("phone", "03172/89068");
		values.put("homepage", "");
		values.put("openingHours", "Mo-Do 08.00 - 02.00 h, Fr-Sa 08.00-04.00h");
		values.put("latitude", "47.2182200");
		values.put("longitude", "15.6234372");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Loder Alois");
		values.put("street", "Weizberg 7");
		values.put("phone", "03172/3924");
		values.put("homepage", "");
		values.put("openingHours",
				"Mo-Fr 17.00-24.00 h, Sa 11.00-24.00 h, So 08.00-22.00 h");
		values.put("latitude", "47.2237116");
		values.put("longitude", "15.6358330");
		values.put("outdoorSpace", "Nein");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "B48");
		values.put("street", "Birkfelder Straße 48");
		values.put("phone", "n.a.");
		values.put("homepage", "");
		values.put("openingHours", "Fr - Sa ab 21.00 h");
		values.put("latitude", "47.2189704");
		values.put("longitude", "15.6327189");
		values.put("outdoorSpace", "Nein");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Roadhouse");
		values.put("street", "Kapruner Generator Straße 22");
		values.put("phone", "0664/6440909");
		values.put("homepage", "http://roadhouse-weiz.at/");
		values.put("openingHours", "Mi - Sa ab 17.00 h, So ab 14.00 h");
		values.put("latitude", "47.2193496");
		values.put("longitude", "15.6276644");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Strofbaunk");
		values.put("street", "Hauptplatz 18");
		values.put("phone", "03172/38412");
		values.put("homepage", "");
		values.put("openingHours",
				"Mo,Mi,Fr 10.00-23.00 h, Di,Do 16.00-23.00 h, Sa 10.00-13.00 16.00-23.00 h");
		values.put("latitude", "47.2169132");
		values.put("longitude", "15.6222637");
		values.put("outdoorSpace", "Ja");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		values.put("name", "Tollhaus");
		values.put("street", "Kapruner Generator Straße 22");
		values.put("phone", "n.a.");
		values.put("homepage", "http://bollwerk.at/");
		values.put("openingHours", "Mi,Fr,Sa ab 21.00 h");
		values.put("latitude", "47.2193496");
		values.put("longitude", "15.6276644");
		values.put("outdoorSpace", "Nein");
		values.put("notes", "");
		db.insert("pubs", // table
				null, // nullColumnHack
				values); // key/value -> keys = column names/ values = column
							// values

		// 4. close
		db.close();
	}

	public ArrayList<String> getPubNamesBySearchString(String pubName) {

		ArrayList<String> pubList = new ArrayList<String>();
		String selectQuery = "SELECT * FROM PUBS WHERE NAME LIKE '%" + pubName
				+ "%'";

		Log.i("statement: ", selectQuery);

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Log.i("Status: ", "found a record");
				pubList.add(cursor.getString(1));
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		// return contact list
		return pubList;
	}

	public Pub getPubByName(String pubName) {

		Pub pub = new Pub();
		String selectQuery = "SELECT * FROM PUBS WHERE NAME = '" + pubName
				+ "'";

		Log.i("statement: ", selectQuery);

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Log.i("Status: ", "found a record");
				pub.setName(cursor.getString(1));
				pub.setStreet(cursor.getString(2));
				pub.setPhone(cursor.getString(3));
				pub.setHomepage(cursor.getString(4));
				pub.setOpeningHours(cursor.getString(5));
				pub.setLatitude(cursor.getString(6));
				pub.setLongitude(cursor.getString(7));
				pub.setOutdoorSpace(cursor.getString(8));
				pub.setNotes(cursor.getString(9));
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		// return contact list
		return pub;
	}

}
